﻿using System;
using System.Collections.Generic;
using System.Data;
using Spider.Data;
namespace Spider.DataAccess
{
    public class StockDAL
    {
        public static dynamic GetStocks(int pageIndex, int pageSize, bool isConcern)
        {
            dynamic obj = new SmartObject();
            CommonDbHelper helper = new CommonDbHelper();
            using (IDbConnection con = helper.CreateDbConnection())
            {
                con.Open();
                IDbCommand cmd = helper.CreateDbCommand("querystock", CommandType.StoredProcedure, con);
                cmd.Parameters.Add(helper.CreateInDbDataParameter("pageindex", pageIndex));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("pagesize", pageSize));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("isconcern", isConcern));
                cmd.Parameters.Add(helper.CreateOutDbDataParameter("recordcount"));
                obj.rows = cmd.ExecuteList();
                obj.total = (int)cmd.Parameters.GetDbParameter("recordcount").Value;
            }
            return obj;
        }
        public static void InsertStock(dynamic stock)
        {
            CommonDbHelper helper = new CommonDbHelper();
            using (IDbConnection con = helper.CreateDbConnection())
            {
                con.Open();
                IDbCommand cmd = helper.CreateDbCommand("insert into stock(scode,sname,szonecode,sconcern) values(@scode,@sname,@szonecode,@sconcern)", con);
                object scode = stock.scode;
                object sname = stock.sname;
                object szonecode = stock.szonecode;
                object sconcern = stock.sconcern;
                cmd.Parameters.Add(helper.CreateInDbDataParameter("scode", scode));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("sname",sname));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("szonecode", szonecode));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("sconcern", sconcern));
                cmd.ExecuteNonQuery();
            }
        }
        public static void UpdateStock(dynamic stock)
        {
            CommonDbHelper helper = new CommonDbHelper();
            using (IDbConnection con = helper.CreateDbConnection())
            {
                con.Open();
                IDbCommand cmd = helper.CreateDbCommand("update stock set scode=@scode,sname=@sname,szonecode=@szonecode,sconcern=@sconcern  where sid=@sid", con);
                object scode = stock.scode;
                object sname = stock.sname;
                object szonecode = stock.szonecode;
                object sconcern = stock.sconcern;
                object sid = stock.sid;
                cmd.Parameters.Add(helper.CreateInDbDataParameter("scode", scode));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("sname", sname));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("szonecode", szonecode));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("sconcern", sconcern));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("sid", sid));
                cmd.ExecuteNonQuery();
            }
        }
        public static void InsertTrade(dynamic trade)
        {
            CommonDbHelper helper = new CommonDbHelper();
            using (IDbConnection con = helper.CreateDbConnection())
            {
                con.Open();
                using (IDbTransaction trans = con.BeginTransaction())
                {
                    IDbCommand cmd = helper.CreateDbCommand("insert into trade set openprice=@openprice,closeprice=@closeprice,volume=@volume,turnover=@turnover,tradedate=@tradedate,sid=@sid", con);
                    cmd.Transaction = trans;
                    object openprice = trade.openprice;
                    object closeprice = trade.closeprice;
                    object volume = trade.volume;
                    object turnover = trade.turnover;
                    object tradedate = trade.tradedate;
                    object sid = trade.sid;
                    cmd.Parameters.Add(helper.CreateInDbDataParameter("openprice", openprice));
                    cmd.Parameters.Add(helper.CreateInDbDataParameter("closeprice", closeprice));
                    cmd.Parameters.Add(helper.CreateInDbDataParameter("volume", volume));
                    cmd.Parameters.Add(helper.CreateInDbDataParameter("turnover", turnover));
                    cmd.Parameters.Add(helper.CreateInDbDataParameter("tradedate", tradedate));
                    cmd.Parameters.Add(helper.CreateInDbDataParameter("sid", sid));
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                }
            }
        }
        public static dynamic GetStockHistory(int sid, int month,int page,int rows)
        {
            CommonDbHelper helper = new CommonDbHelper();
            using (IDbConnection con=helper.CreateDbConnection())
            {
                con.Open();
                IDbCommand cmd = helper.CreateDbCommand("select s.scode as code,s.sname as name,t.openprice as openprice,t.closeprice as closeprice,t.tradedate as tradedate from stock as s inner join trade as t on s.sid=t.sid where s.sid=@sid and t.tradedate>=@tradedate order by tradedate desc limit @skip,@count", con);
                cmd.Parameters.Add(helper.CreateInDbDataParameter("sid",sid));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("tradedate", DateTime.Now.AddMonths(0-month)));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("skip",(page-1)*rows));
                cmd.Parameters.Add(helper.CreateInDbDataParameter("count", rows));
                return cmd.ExecuteList();
                con.Close();
            }
        }
    }
}
